package moveMysql;


import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.ObjectOutputStream;
import java.sql.*;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;

import static configure.OutputConfig.*;
import static moveMysql.Constant.*;

public class OutputMysql {

    private static Connection conn = null;
    private static Statement stmt = null;

    public static void main(String[] args) {
        LinkedList<Long> list = new LinkedList<>();
        list.add(new Long(55));
        mysqlDataOut(list);

    }

    public static void mysqlDataOut() {
        //获取源mysql数据
        LinkedList<Subsite> subsites = getDataFromSourceMysqlSubsite(getSubsiteDataSql);
        LinkedList<Subject> subjects = getDataFromSourceMysqlSubject(getSubjectDataSql);
        LinkedList<CaseEntity> caseEntities = getDataFromSourceMysqlCaseEntity(getCaseEntityDataSql);
        LinkedList<CaseExtends> caseExtends = getDataFromSourceMysqlCaseExtends(getCaseExtendsDataSql);
        LinkedList<Target> targets = getDataFromSourceMysqlTarget(getTargetDataSql);
        LinkedList<TargetExtends> targetExtends = getDataFromSourceMysqlTargetExtends(getTargetExtendsDataSql);
        LinkedList<Long> targetIds = new LinkedList<>();
        //挑选出有效target
        for (CaseEntity caseEntity : caseEntities) {
            targetIds.add(caseEntity.getApp_id());
        }
        //筛选case有效的target
        for (int i = 0; i < targets.size(); i++) {
            if (!targetIds.contains(targets.get(i).getId())) {
                targets.remove(i);
                i--;
            }
        }
        //筛选case有效的targetExtends
        for (int i = 0; i < targetExtends.size(); i++) {
            if (!targetIds.contains(targetExtends.get(i).getTargetId())) {
                targetExtends.remove(i);
                i--;
            }
        }
        HashMap<String, Object> maps = new HashMap<>();
        //向map对象中添加要保存的数据
        maps.put("subsites", subsites);
        maps.put("subjects", subjects);
        maps.put("caseEntities", caseEntities);
        maps.put("caseExtends", caseExtends);
        maps.put("targets", targets);
        maps.put("targetExtends", targetExtends);
        //写数据
        try {
            outputFile(maps);
        } catch (Exception e) {
            System.out.println("写文件失败:" + e.getMessage());
            e.printStackTrace();
        }

    }

    public static LinkedList<Long> mysqlDataOut(List<Long> caseIds) {
        LinkedList<Subsite> subsites = getDataFromSourceMysqlSubsite(getSubsiteDataSql);
        LinkedList<Subject> subjects = getDataFromSourceMysqlSubject(getSubjectDataSql);
        LinkedList<CaseEntity> caseEntities = new LinkedList<>();
        LinkedList<CaseExtends> caseExtends = new LinkedList<>();
        LinkedList<Target> targets = getDataFromSourceMysqlTarget(getTargetDataSql);
        LinkedList<TargetExtends> targetExtends = getDataFromSourceMysqlTargetExtends(getTargetExtendsDataSql);
        LinkedList<Long> targetIds = new LinkedList<>();
        for (Long caseId : caseIds) {
            CaseEntity tempCaseEntity = getDataFromSourceMysqlCaseEntityByCaseId(caseId, getCaseEntityDataSqlById);
            CaseExtends tempCaseExtends = getDataFromSourceMysqlCaseExtendsByCaseId(caseId, getCaseExtendsDataSqlById);
            if (null != tempCaseEntity) {
                targetIds.add(tempCaseEntity.getApp_id());
                caseEntities.add(tempCaseEntity);
            }
            if (null != tempCaseExtends)
                caseExtends.add(tempCaseExtends);
        }
        //筛选case有效的target
        for (int i = 0; i < targets.size(); i++) {
            if (!targetIds.contains(targets.get(i).getId())) {
                targets.remove(i);
                i--;
            }
        }
        //筛选case有效的targetExtends
        for (int i = 0; i < targetExtends.size(); i++) {
            if (!targetIds.contains(targetExtends.get(i).getTargetId())) {
                targetExtends.remove(i);
                i--;
            }
        }
        HashMap<String, Object> maps = new HashMap<>();
        //向map对象中添加要保存的数据
        maps.put("subsites", subsites);
        maps.put("subjects", subjects);
        maps.put("caseEntities", caseEntities);
        maps.put("caseExtends", caseExtends);
        maps.put("targets", targets);
        maps.put("targetExtends", targetExtends);
        //写数据
        try {
            outputFile(maps);
        } catch (Exception e) {
            System.out.println("写文件失败:" + e.getMessage());
            e.printStackTrace();
        }
        return targetIds;
    }

    /**
     * @param
     * @return java.util.LinkedList<moveMysql.CaseEntity>
     * @Description 导出CaseEntity表中数据
     * @author 孙加辉
     * @date 2018/10/29 20:01
     */
    public static LinkedList<CaseEntity> getDataFromSourceMysqlCaseEntity(String sql) {
        LinkedList<CaseEntity> caseEntities = new LinkedList<>();
        //连接源mysql
        try {
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);
            //连接数据库
            conn = DriverManager.getConnection(DB_URL_SOURCE, USER_SOURCE, PASS_SOURCE);
        } catch (Exception e) {
            System.out.println("源数据库连接失败:" + e.getMessage());
        }
        //查询
        try {
            //实例化Statement对象
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                CaseEntity temp = new CaseEntity(
                        rs.getLong("id"),
                        rs.getString("name"),
                        rs.getInt("difficulty"),
                        rs.getInt("domain"),
                        rs.getString("description"),
                        rs.getLong("subject_id"),
                        rs.getString("case_id"),
                        rs.getBoolean("is_public"),
                        rs.getTimestamp("create_time"),
                        rs.getLong("app_id"),
                        rs.getBoolean("is_deleted"),
                        rs.getLong("owner_id"),
                        rs.getShort("platform"),
                        rs.getString("properties"),
                        rs.getLong("subsite_id")
                );
                caseEntities.add(temp);
            }
            // 完成后关闭
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return caseEntities;
    }//LinkedList<CaseEntity>


    public static CaseEntity getDataFromSourceMysqlCaseEntityByCaseId(Long caseId, String sql) {
        CaseEntity caseEntity = new CaseEntity();
        //连接源mysql
        try {
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);
            //连接数据库
            conn = DriverManager.getConnection(DB_URL_SOURCE, USER_SOURCE, PASS_SOURCE);
        } catch (Exception e) {
            System.out.println("源数据库连接失败:" + e.getMessage());
        }
        //查询
        try {
            //实例化Statement对象
            stmt = conn.createStatement();
            PreparedStatement pstmt = (PreparedStatement) (conn.prepareStatement(sql));
            pstmt.setLong(1, caseId);
            //pstmt.executeUpdate();
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                caseEntity.setId(rs.getLong("id"));
                caseEntity.setName(rs.getString("name"));
                caseEntity.setDifficulty(rs.getInt("difficulty"));
                caseEntity.setDomain(rs.getInt("domain"));
                caseEntity.setDescription(rs.getString("description"));
                caseEntity.setSubject_id(rs.getLong("subject_id"));
                caseEntity.setCase_id(rs.getString("case_id"));
                caseEntity.setIs_public(rs.getBoolean("is_public"));
                caseEntity.setCreate_time(rs.getTimestamp("create_time"));
                caseEntity.setApp_id(rs.getLong("app_id"));
                caseEntity.setIs_deleted(rs.getBoolean("is_deleted"));
                caseEntity.setOwner_id(rs.getLong("owner_id"));
                caseEntity.setPlatform(rs.getShort("platform"));
                caseEntity.setProperties(rs.getString("properties"));
                caseEntity.setSubsite_id(rs.getLong("subsite_id"));
            } else {
                caseEntity = null;
                System.out.println("case_entity中未查询到id为:" + caseId + "的记录");
            }
            // 完成后关闭
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return caseEntity;
    }

    /**
     * @param
     * @return java.util.LinkedList<moveMysql.CaseExtends>
     * @Description 从case_extends表中读取数据
     * @author 孙加辉
     * @date 2018/10/31 10:22
     */
    public static LinkedList<CaseExtends> getDataFromSourceMysqlCaseExtends(String sql) {
        LinkedList<CaseExtends> caseExtends = new LinkedList<>();
        //连接源mysql
        try {
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);
            //连接数据库
            conn = DriverManager.getConnection(DB_URL_SOURCE, USER_SOURCE, PASS_SOURCE);
        } catch (Exception e) {
            System.out.println("源数据库连接失败:" + e.getMessage());
        }
        //查询
        try {
            //实例化Statement对象
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                CaseExtends temp = new CaseExtends(
                        rs.getShort("platform"),
                        rs.getString("properties"),
                        rs.getLong("subsite_id"),
                        rs.getLong("target_id"),
                        rs.getLong("id")
                );
                caseExtends.add(temp);
            }
            // 完成后关闭
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return caseExtends;
    }

    public static CaseExtends getDataFromSourceMysqlCaseExtendsByCaseId(Long caseId, String sql) {
        CaseExtends caseExtends = new CaseExtends();
        //连接源mysql
        try {
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);
            //连接数据库
            conn = DriverManager.getConnection(DB_URL_SOURCE, USER_SOURCE, PASS_SOURCE);
        } catch (Exception e) {
            System.out.println("源数据库连接失败:" + e.getMessage());
        }
        //查询
        try {
            //实例化Statement对象
            stmt = conn.createStatement();
            PreparedStatement pstmt = (PreparedStatement) (conn.prepareStatement(sql));
            pstmt.setLong(1, caseId);
            //pstmt.executeUpdate();
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                caseExtends.setId(rs.getLong("id"));
                caseExtends.setPlatform(rs.getShort("platform"));
                caseExtends.setProperties(rs.getString("properties"));
                caseExtends.setSubsiteId(rs.getLong("subsite_id"));
                caseExtends.setTargetId(rs.getLong("target_id"));
            } else {
                caseExtends = null;
                System.out.println("case_extends中未查询到id为:" + caseId + "的记录");
            }
            // 完成后关闭
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return caseExtends;
    }

    /**
     * @param
     * @return java.util.LinkedList<moveMysql.Target>
     * @Description
     * @author 孙加辉
     * @date 2018/10/29 20:20
     */
    public static LinkedList<Target> getDataFromSourceMysqlTarget(String sql) {
        LinkedList<Target> targets = new LinkedList<>();
        //连接源mysql
        try {
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);
            //连接数据库
            conn = DriverManager.getConnection(DB_URL_SOURCE, USER_SOURCE, PASS_SOURCE);
        } catch (Exception e) {
            System.out.println("源数据库连接失败:" + e.getMessage());
        }
        //查询
        try {
            //实例化Statement对象
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                Target temp = new Target(
                        rs.getLong("id"),
                        rs.getBoolean("is_public"),
                        rs.getString("name"),
                        rs.getString("url"),
                        rs.getLong("subsite_id"),
                        rs.getInt("status"),
                        rs.getTimestamp("create_time"),
                        rs.getLong("owner_id"),
                        rs.getString("message")
                );
                targets.add(temp);
            }
            // 完成后关闭
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return targets;
    }

    /**
     * @param
     * @return java.util.LinkedList<moveMysql.TargetExtends>
     * @Description
     * @author 孙加辉
     * @date 2018/10/29 20:19
     */
    public static LinkedList<TargetExtends> getDataFromSourceMysqlTargetExtends(String sql) {
        LinkedList<TargetExtends> targetExtends = new LinkedList<>();
        //连接源mysql
        try {
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);
            //连接数据库
            conn = DriverManager.getConnection(DB_URL_SOURCE, USER_SOURCE, PASS_SOURCE);
        } catch (Exception e) {
            System.out.println("源数据库连接失败:" + e.getMessage());
        }
        //查询
        try {
            //实例化Statement对象
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                //System.out.println(rs.getString("id"));
                TargetExtends temp = new TargetExtends(
                        rs.getLong("id"),
                        rs.getLong("target_id"),
                        rs.getString("property_name"),
                        rs.getString("property_value")
                );
                targetExtends.add(temp);
            }
            // 完成后关闭
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return targetExtends;
    }

    /**
     * @param
     * @return void
     * @Description 将对象保存到文件中
     * @author 孙加辉
     * @date 2018/10/29 20:01
     */

    /**
     * @param
     * @return java.util.LinkedList<moveMysql.Subject>
     * @Description 读取subject表中数据
     * @author 孙加辉
     * @date 2018/10/30 17:31
     */
    public static LinkedList<Subject> getDataFromSourceMysqlSubject(String sql) {
        LinkedList<Subject> subjects = new LinkedList<>();
        //连接源mysql
        try {
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);
            //连接数据库
            conn = DriverManager.getConnection(DB_URL_SOURCE, USER_SOURCE, PASS_SOURCE);
        } catch (Exception e) {
            System.out.println("源数据库连接失败:" + e.getMessage());
        }
        //查询
        try {
            //实例化Statement对象
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                Subject temp = new Subject(
                        rs.getLong("id"),
                        rs.getString("name"),
                        rs.getString("type_name"),
                        rs.getString("target_url"),
                        rs.getLong("subsite_id")
                );
                subjects.add(temp);
            }
            // 完成后关闭
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return subjects;
    }

    /**
     * @param
     * @return java.util.LinkedList<moveMysql.Subsite>
     * @Description 读取subsite表中数据
     * @author 孙加辉
     * @date 2018/10/30 17:22
     */
    public static LinkedList<Subsite> getDataFromSourceMysqlSubsite(String sql) {
        LinkedList<Subsite> subsites = new LinkedList<>();
        //连接源mysql
        try {
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);
            //连接数据库
            conn = DriverManager.getConnection(DB_URL_SOURCE, USER_SOURCE, PASS_SOURCE);
        } catch (Exception e) {
            System.out.println("源数据库连接失败:" + e.getMessage());
        }
        //查询
        try {
            //实例化Statement对象
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                Subsite temp = new Subsite(
                        rs.getLong("id"),
                        rs.getString("name"),
                        rs.getString("base_url"),
                        rs.getString("type"),
                        rs.getString("worker_entrance_url"),
                        rs.getString("manager_entrance_url")
                );
                subsites.add(temp);
            }
            // 完成后关闭
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return subsites;
    }

    public static void outputFile(Object objects) throws IOException {
        File file = new File(SAVE_FILEPATH + SAVE_FILENAME);
        ObjectOutputStream objOut = new ObjectOutputStream(new FileOutputStream(file));
        objOut.writeObject(objects);
    }


    public static void test() {
        String testSql = "select c.* from case_entity c,visit_control v where (v.entity_type = 'case' and c.id = v.entity_id AND v.valid = 1)";
    }
}
